import os
import tkinter as tk
from tkinter import messagebox

#利用容错，来判断包是否存在，如果不存在使用os.system()使用pip命令下载包
try:
    import pymysql
except:
    #下载pymysql
    os.system("pip install pymysql")
    print("------------------------------")
    print("pymysql包缺失，下载中。。。")
    print("-----------------------------")

try:
    import ttkbootstrap
except:
    os.system('pip install ttkbootstrap')
    print("------------------------------")
    print("ttkbootstrap包缺失，下载中。。。")
    print("-----------------------------")

import ttkbootstrap as ttk
from ttkbootstrap.constants import *
from ttkbootstrap.dialogs import *
from ttkbootstrap.toast import ToastNotification
import tkinter as tk
import pymysql
import json
import logging
import time
from PIL import Image,ImageTk

class MyApp:
    def __init__(self, root):
        self.root = root
        self.root.title("图书管理系统")
        self.is_night = False
        self.user = None
        self.pwd = None
        self.mysql_init() #执行mysql初始化函数
        self.login_ui()

    def login_ui(self):

        def login():
            cursos = self.link().cursor()
            cursos.execute("SELECT * FROM bookAdmin.user WHERE user = %s and pass=md5(%s)", (self.username_entry.get(),self.password_entry.get()))
            if len(cursos.fetchall()) > 0:
                Messagebox.show_question("登录成功")

                #储存账号密码
                self.user = self.username_entry.get()
                self.pwd = self.password_entry.get()

                self.main_ui()
                frame.destroy() #关闭当前页面
            else:
                Messagebox.show_info("账号密码错误")

        frame = ttk.Frame(self.root, padding="20")
        frame.grid(row=0, column=0, padx=50, pady=50)

        ttk.Label(frame, text="Login", font=(None, 25)).grid(row=0, column=0)
        v1 = ttk.Labelframe(frame, text="Username")
        v1.grid(row=1, column=0, columnspan=2)
        self.username_entry = ttk.Entry(v1, font=(None, 15))
        self.username_entry.pack()

        v2 = ttk.Labelframe(frame, text="Password")
        v2.grid(row=2, column=0, columnspan=2)
        self.password_entry = ttk.Entry(v2, show="*", font=(None, 15))
        self.password_entry.pack()

        ttk.Button(frame, text="登录", command=login).grid(row=3, column=0, padx=5, pady=10)
        ttk.Button(frame, text="跳转注册", command=self.register_ui).grid(row=3, column=1, padx=5, pady=10)

    def register_ui(self):

        def register():
            if self.username_entry.get() == "" or self.password_entry.get() == "":
                Messagebox.show_info("请输入用户名和密码")
            else:
                cursos = self.link().cursor()
                cursos.execute("insert into bookAdmin.user values (%s,md5(%s),1,%s)", (self.username_entry.get(),self.password_entry.get(),time.strftime("%Y-%m-%d")))
                if cursos.rowcount == 1:
                    Messagebox.show_question("注册成功")
                else:
                    Messagebox.show_info("注册失败")

        frame = ttk.Frame(self.root, padding="20")
        frame.grid(row=0, column=0, padx=50, pady=50)

        ttk.Label(frame, text="Register", font=(None, 25)).grid(row=0, column=0)
        v1 = ttk.Labelframe(frame, text="Username")
        v1.grid(row=1, column=0, columnspan=2)
        self.username_entry = ttk.Entry(v1, font=(None, 15))
        self.username_entry.pack()

        v2 = ttk.Labelframe(frame, text="Password")
        v2.grid(row=2, column=0, columnspan=2)
        self.password_entry = ttk.Entry(v2, show="*", font=(None, 15))
        self.password_entry.pack()

        ttk.Button(frame, text="注册", command=register).grid(row=3, column=0, padx=5, pady=10)
        ttk.Button(frame, text="返回登录", command=self.login_ui).grid(row=3, column=1, padx=5, pady=10)

    #主页面UI
    def main_ui(self):

        def set_ui():

            # 开启夜间模式
            def night():
                if self.is_night:
                    self.is_night = False
                    ttk.Style("journal")
                else:
                    self.is_night = True
                    ttk.Style("darkly")

            def endLogin():
                self.login_ui()
                parent_frame.destroy()

            frame = ttk.Frame(nb,padding='20')
            frame.grid(row=0,column=0)
            ttk.Label(frame, text="Setting", font=(None, 25)).grid(row=0, column=0)
            v1 = ttk.Frame(frame)
            v1.grid(row=1, column=0)
            ttk.Label(v1,text="夜间模式").pack(side=LEFT)
            night_check = ttk.Checkbutton(v1,bootstyle=(SUCCESS, ROUND, TOGGLE),command=night)
            night_check.pack(side=RIGHT)

            v2 = ttk.Frame(frame)
            v2.grid(row=2, column=0)
            ttk.Button(v2, text="退出登录", command=endLogin).grid(row=0, column=0,padx=50)
            

            return frame
        
        def addYet():
            def add_book():
                # 获取输入字段的内容
                book_title = title_entry.get()
                release_date = date_entry.get()
                author = author_entry.get()
                isbn = isbn_entry.get()

                if book_title ==  "" or release_date == "" or author == "" or isbn == "":
                    messagebox.showinfo("Error", "不能留空")
                else:
                    import random
                    cursor = self.link().cursor()
                    cursor.execute("INSERT INTO bookAdmin.book (book_id,title,publication_year,author,isbn,genre,available,checked_out_by_member_id,checked_out_date) VALUES (%s, %s, %s, %s, %s, 'Fiction', 1, NULL, NULL)",(random.randint(111111,9999999),book_title, release_date, author, isbn))
                    if cursor.rowcount > 0:
                        messagebox.showinfo("Success", "添加成功")
                    else:
                        messagebox.showinfo("Error", "添加失败")
                    root.deiconify()

            # 创建主窗口
            root = tk.Tk()
            root.title("添加 Book")
            # 创建标签和输入字段
            tk.Label(root, text="书名:").grid(row=0, column=0, padx=10, pady=10)
            title_entry = tk.Entry(root)
            title_entry.grid(row=0, column=1, padx=10, pady=10)
            tk.Label(root, text="发行日期 (YYYY):").grid(row=1, column=0, padx=10, pady=10)
            date_entry = tk.Entry(root)
            date_entry.grid(row=1, column=1, padx=10, pady=10)
            tk.Label(root, text="作者:").grid(row=2, column=0, padx=10, pady=10)
            author_entry = tk.Entry(root)
            author_entry.grid(row=2, column=1, padx=10, pady=10)
            tk.Label(root, text="ISBN:").grid(row=3, column=0, padx=10, pady=10)
            isbn_entry = tk.Entry(root)
            isbn_entry.grid(row=3, column=1, padx=10, pady=10)
            # 创建添加按钮
            add_button = tk.Button(root, text="Add Book", command=add_book)
            add_button.grid(row=4, column=0, columnspan=2, pady=10)
            # 启动事件循环
            root.mainloop()
            ...

        
        def main_ui():
            frame = ttk.Frame(nb)
            frame.grid(row=0, column=0, sticky="ew")

            v1 = ttk.Frame(frame, padding=5)
            v1.grid(row=0, column=0)
            search_entry = ttk.Entry(v1, width=30)
            search_entry.grid(row=0, column=0, columnspan=3, sticky="ew")
            ttk.Button(v1, text="搜索",command= lambda : book_list(search_entry.get())).grid(row=0, column=3, padx=10)
            ttk.Button(v1, text="添加",command=addYet).grid(row=0, column=4)
            v1.columnconfigure(0, weight=1)

            

            canvas = ttk.Canvas(frame,width=450,height=450)
            canvas.grid(row=1, column=0, sticky="nsew")  # Place the canvas in a new row

            myscrollbar = ttk.Scrollbar(frame, orient="vertical", command=canvas.yview)  # Create a vertical scrollbar
            myscrollbar.grid(row=1, column=1, sticky="ns")  # Place the scrollbar in the same row as the canvas

            v2 = ttk.Frame(canvas, padding=5)
            v2.grid(row=0, column=0, sticky="ew")

            canvas.create_window((0, 0), window=v2, anchor='nw')

            def on_configure(event):
                canvas.configure(scrollregion=canvas.bbox("all"))

            v2.bind("<Configure>", on_configure)

            
           
            def delete_book(bid):
                print(bid)
                currsos = self.link().cursor()
                currsos.execute("select admin from bookAdmin.user where user=%s and pass=md5(%s)",(self.user,self.pwd)) #查询权限
                if int(currsos.fetchall()[0][0]) == 2: #如果为管理员 1为普通用户，2为管理员
                    currsos.execute("delete from bookAdmin.book where book_id=%s",(bid))
                    Messagebox.show_warning("删除成功","提示")
                    book_list()
                else:
                    Messagebox.show_warning("当前账号权限不足","提示")
                ...

            #借书函数
            def borrow_book(bid):
                currsos = self.link().cursor()
                currsos.execute("select available,checked_out_by_member_id,checked_out_date from bookAdmin.book where book_id=%s",(bid)) #查询图书借书细信息
                data = currsos.fetchall()[0]
                if int(data[0]) != 1 :
                    Messagebox.show_warning(f"该本图书已被{data[1]}借阅,于{data[2]}被借阅","提示")
                else:
                    currsos.execute("update bookAdmin.book set available=0,checked_out_by_member_id=%s,checked_out_date=%s where book_id=%s",(self.user,time.strftime("%Y-%m-%d"),bid))
                    Messagebox.show_warning("借书成功","提示")
                ...
            def book_list(isbn = None):
                print((isbn))
                currsos = self.link().cursor()
                if isbn == None or isbn == '':
                    currsos.execute("select * from bookAdmin.book")
                else:
                    currsos.execute("select * from bookAdmin.book where isbn=%s",(isbn))

                #遍历添加前，清空父组件的子组件
                for child in v2.winfo_children(): #取所有子组件
                    child.destroy()

                for i in currsos.fetchall():
                    
                    vv1 = ttk.Frame(v2)
                    vv1.grid(row=i[0] - 1,column=0,sticky="ew")
                    ttk.Label(vv1,text=i[1],font=(None,17)).grid(row=0,column=0) #书名

                    vv2 = ttk.Frame(vv1)
                    vv2.grid(row=1,column=0)
                    ttk.Label(vv2,text=f"作者：{i[2]}   出版日期：{i[3]}",foreground="#808080").pack(side="left")

                    vv3 = ttk.Frame(vv1) 
                    vv3.grid(row=2,column=0)
                    ttk.Label(vv3,text=f"ISBN：{i[4]}",foreground="#808080").pack(side="left")

                    vv4 = ttk.Frame(vv1)
                    vv4.grid(row=3, column=0,pady=10)
                    ttk.Button(vv4, text="借阅",command= lambda bid = i[0] : borrow_book(bid=bid)).pack(side="left")
                    ttk.Button(vv4, text="删除",bootstyle="error",command= lambda bid = i[0] : delete_book(bid=bid)).pack(side="left",padx=5)

                    separator = ttk.Separator(vv1, orient="horizontal").grid(sticky="ew") #分割线

            book_list()

            return frame
        
        def mine_ui():

            #借阅列表获取
            def borrow_list():
                cursor = self.link().cursor()
                #查询本账户已经借阅的书本
                #只取 书名、isbn号、借书日期
                cursor.execute("SELECT title,isbn,checked_out_date FROM bookAdmin.book WHERE checked_out_by_member_id=%s",(self.user))
                return cursor.fetchall()

            def update_borrow_list():
                tree.delete(*tree.get_children())
                for book in borrow_list():
                    title, isbn, checked_out_date = book
                    tree.insert("", "end", values=(title, isbn, checked_out_date))
                    tree.pack()
            # 修改密码按钮
            def change_password():
                # 在这里添加修改密码的逻辑
                pass
            frame = ttk.Frame(nb)
            frame.grid(row=0,column=0,sticky="ew")

            ttk.Label(frame, text="个人信息", font=(None, 25)).pack(padx=5,pady=5)

            # 显示账号
            ttk.Label(frame, text=f"账号：{self.user}",font=(None,20)).pack()

            ttk.Button(frame, text="修改密码", command=change_password).pack()

            ttk.Button(frame,text="更新借阅数据", command=update_borrow_list).pack(pady=5)
            
            tree = ttk.Treeview(frame, columns=(0,1,2))
            
            tree.heading(0, text="书名")
            tree.heading(1, text="ISBN号")
            tree.heading(2, text="借书日期")

            for book in borrow_list():
                title, isbn, checked_out_date = book
                tree.insert("", "end", values=(title, isbn, checked_out_date))
            tree.pack(pady=5)
            return frame


        parent_frame = ttk.Frame(self.root,width=500,height=500)
        parent_frame.grid(row=0, column=0)
        
        nb = ttk.Notebook(parent_frame)
        
        nb.add(main_ui(), text='主页')
        nb.add(mine_ui(), text='我的')
        nb.add(set_ui(), text='设置')

        nb.pack()

    # 拿到数据库连接对象
    def link(self):
        try:
            mysql_config = json.load(open("config/db.con", mode="r"))
            connect = pymysql.connect(host=mysql_config['host'], user=mysql_config['user'], password=mysql_config['pass'])
        except Exception as e:
            if 'MySQL server' in str(e):
                Messagebox.show_error("你还未开启Mysql环境","提示")
            elif "(using password: YES)" in str(e):
                self.mysql_config_update_ui()             
            else:
                ...

        return connect

    #mysql初始化函数
    def mysql_init(self):
        # 连接数据库
        connect = self.link()
        cursor = connect.cursor()
        cursor.execute("CREATE DATABASE IF NOT EXISTS bookAdmin") #创建库
        #创建book表
        cursor.execute("CREATE TABLE IF NOT EXISTS bookAdmin.book (book_id int(11) NOT NULL COMMENT '图书ID',title varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '书名',author varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT '作者',publication_year int(11) DEFAULT NULL COMMENT '出版年份',isbn varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'ISBN号',genre varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '类型',available tinyint(1) DEFAULT '1' COMMENT '可借状态',checked_out_by_member_id varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,checked_out_date date DEFAULT NULL COMMENT '借阅日期',PRIMARY KEY (book_id),KEY checked_out_by_member_id (checked_out_by_member_id)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='图书表'")
        #创建user表
        cursor.execute("CREATE TABLE IF NOT EXISTS bookAdmin.user (user varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,pass varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,admin int(11) DEFAULT NULL,time varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci")
        # 初始化管理员账号密码
        cursor.execute("select * from bookAdmin.user")
        if len(cursor.fetchall()) == 0:
            cursor.execute("INSERT INTO bookAdmin.user (user,pass,admin) VALUES ('admin',md5(123456),2)")
        #初始化书本数据
        cursor.execute("select * from bookAdmin.book")
        if len(cursor.fetchall()) == 0:
            cursor.execute("INSERT INTO bookAdmin.book VALUES (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 1925, '9780743273565', 'Classic', 1, NULL, NULL),(2, 'To Kill a Mockingbird', 'Harper Lee', 1960, '9780061120084', 'Fiction', 1, NULL, NULL),(3, '1984', 'George Orwell', 1949, '9780451524935', 'Dystopian', 1, NULL, NULL),(4, 'Pride and Prejudice', 'Jane Austen', 1813, '9780141439518', 'Romance', 1, NULL, NULL),(5, 'The Catcher in the Rye', 'J.D. Salinger', 1951, '9780316769488', 'Coming-of-age', 1, NULL, NULL)")

    def mysql_config_update_ui(self):
        def dis():
            root.destroy()
        def update():
            j["host"] = host.get()
            j['user'] = user.get()
            j['pass'] = pwd.get()
            if host.get() == "" or user.get() == "" or pwd.get() == "":
                Messagebox.show_info(title="警告", message="请输入完整信息")
            else:
                with open("config/db.con",mode="w") as f:
                    f.write(json.dumps(j, indent=4))
                Messagebox.show_question(title="成功", message="修改成功")
                root.destroy()

        
        j = json.load(open("config/db.con", mode="r"))


        frame = ttk.Frame(self.root, padding="20")
        frame.grid(row=0, column=0, padx=50, pady=50)
        ttk.Label(frame, text="数据库信息修改").grid(row=0, column=0,padx=10, pady=10)

        v1 = ttk.LabelFrame(frame, text="数据库HOST:")
        v1.grid(row=1,column=0,columnspan=2,pady=5,padx=10)
        host = ttk.Entry(v1)
        host.insert('end', j["host"])
        host.pack()

        v2 = ttk.LabelFrame(frame, text="数据库账号:")
        v2.grid(row=2,column=0,columnspan=2,pady=5,padx=10)
        user = ttk.Entry(v2)
        user.insert('end', j["user"])
        user.pack()

        v3 = ttk.LabelFrame(frame, text="数据库密码:")
        v3.grid(row=3,column=0,columnspan=2,pady=5,padx=10)
        pwd = ttk.Entry(v3)
        pwd.insert('end', j["pass"])
        pwd.pack()

        cancel = ttk.Button(frame, text="取消", width=10, bootstyle=INFO,command=dis)
        cancel.grid(row=4,column=0,pady=5,padx=10)

        update = ttk.Button(frame, text="修改", width=10, bootstyle=SUCCESS,command=update)
        update.grid(row=4,column=1,pady=5,padx=10)


if __name__ == "__main__":
    root = ttk.Window()
    app = MyApp(root)
    root.mainloop()
